# file: $Id: dbConnect.py 68 2009-08-31 00:12:51Z duboulay $
#
# Author: Doug du Boulay <boulay_d@chem.usyd.edu.au>
# Copyright (c) 2009, The University of Sydney
# All rights reserved.
#
# This software is distributed under the BSD license.
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
# 3. The name of the author may not be used to endorse or promote products
#    derived from this software without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
# IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
# OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
# IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
# NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
# DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
# THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

import sys
# these are non standard python extension modules
#import cx_Oracle
#import pyPgSQL
#from pyPgSQL import libpq

# see  http://kb.parallels.com/en/1133   for info on how to set  
#      $PGSQL_DATA_D/postgresql.conf    listen_addresses = '*'
#  and add:
#      $PGSQL_DATA_D/pg_hba.conf        host all all  0.0.0.0/0 md5
# to permit remote tcpip access

DEBUG = 0

class DBConnectException(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

class DBConnectSQLSyntaxException(Exception):
    def __init__(self, value):
        if hasattr(Exception, '__init__'):
            Exception.__init__(self,value)

class DBConnectSQLResultException(Exception):
    def __init__(self, value):
        if hasattr(Exception, '__init__'):
            Exception.__init__(self,value)


class DBConnection:
    """ This is a wrapper around iether pyPgSQL / libpq  or 
cx_Oracle so that we can try to provide a single map:sql interface
in the absence of dbi drivers unixODBC.
    """
    def __init__(self,dummy=None):
        self.debug = DEBUG
	self.simMode = None  # db simulation mode
        if dummy == None or dummy != False: 
            self.dbmode = "db_bypass"
        else:
            self.dbmode = None
        
        
    def pqInit(self, dbhost, dbport, dbname, dbuser, dbpass): 
    
        if self.dbmode == None:
            self.dbmode = "libpq"
        else:
            self.simMode = "libpq"
	self.dbname = dbname
	self.dbhost = dbhost
	self.dbport = dbport
	self.dbuser = dbuser
	self.dbpass = dbpass
	self.dbcnx = None

    def psycopg2Init(self, dbhost, dbport, dbname, dbuser, dbpass): 
        if self.dbmode == None:
            self.dbmode = "psycopg2"
        else:
            self.simMode = "psycopg2"
	self.dbname = dbname
	self.dbhost = dbhost
	self.dbport = dbport
	self.dbuser = dbuser
	self.dbpass = dbpass
	self.dbcnx = None

    def cxInit(self, dbhost, dbport, dbname, dbuser, dbpass): 
        if self.dbmode == None:
            self.dbmode = "cx_Oracle"
        else:
            self.simMode = "cx_Oracle"
	self.dbname = dbname
	self.dbhost = dbhost
	self.dbport = dbport
	self.dbuser = dbuser
	self.dbpass = dbpass
        self.dbstr1 = dbuser + "/" + dbpass + "@" + \
                "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbhost  + \
                ")(PORT=" + dbport + "))(CONNECT_DATA=(SID=" + dbname +" )))"
	self.dbcnx = None
	self.cursor = None


    def connect(self):
        import sys
        # Make a connection to the database and check to see if it succeeded.
        if self.dbmode == "libpq":
            from pyPgSQL import libpq
            try:
                #self.dbcnx = libpq.connect('%s:%s:%s:%s:%s::' % 
                self.dbcnx = libpq.PQconnectdb('host=%s port=%s dbname=%s user=%s password=%s' % 
            	  (self.dbhost, self.dbport, self.dbname, self.dbuser, self.dbpass) )
            except libpq.Error, msg:
                print "Connection to database '%s' failed" % self.dbname
                print msg,
                sys.exit()
        elif self.dbmode == "psycopg2":
            import psycopg2
            try:
                #self.dbcnx = libpq.connect('%s:%s:%s:%s:%s::' % 
                self.dbcnx = psycopg2.connect("host='%s' port='%s' dbname='%s' user='%s' password='%s'" % 
            	  (self.dbhost, self.dbport, self.dbname, self.dbuser, self.dbpass) )
                self.cursor = self.dbcnx.cursor()
                #self.cursor.execute("SET search_path TO scd,public")
            except:
                exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                print "Connection to database '%s' failed" % self.dbname
                print exceptionType, exceptionValue,
                sys.exit()
        elif self.dbmode == "cx_Oracle":
            import cx_Oracle
            try:
                self.dbcnx = cx_Oracle.connect(self.dbstr1)
                self.cursor = self.dbcnx.cursor()
            except cx_Oracle.Error, msg:
                print "Connection to database '%s' failed" % self.dbname
                print msg,
                if __name__ == '__main__':
                  sys.exit()
                raise DBConnectException, msg
        elif self.dbmode == "db_bypass":
                self.dbcnx = DummyDB(self.simMode)
        else:
            raise DBConnectException, "Unrecognised database connection mode" + self.dbmode 

        if self.debug & 1:
            print  "Connected to: ", self.dbname


    def query(self,queryString):
        " evaluate the query and pre-assemble the result, cast failures to our custom exceptions "
        if self.dbmode == "libpq":
            from pyPgSQL import libpq
            # obviously this doesn't work transparently yet!
            try:
                res = self.dbcnx.query(queryString) # [ attribs, text, sub elements ]
            except libpq.Error, msg:
                print "SQL query failed\n%s" % msg,
                raise DBConnectSQLSyntaxException(msg)
                #sys.exit()

            if res.resultStatus != libpq.TUPLES_OK:
                raise DBConnectSQLResultException("ERROR: SQL query failed")
                #print  self.ctnode
            return SQLResult(self.dbmode, res)

        elif self.dbmode == "psycopg2":
            #import libpq
            # obviously this doesn't work transparently yet!
            try:
                res = self.cursor.execute(queryString) # [ attribs, text, sub elements ]
            except:
                exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                print exceptionTraceback
                print "SQL query failed\n%s" % exceptionValue,
                raise DBConnectSQLSyntaxException(exceptionValue)
                #sys.exit()

            #if res.resultStatus != libpq.TUPLES_OK:
            #    raise DBConnectSQLResultException("ERROR: SQL query failed")
            #    #print  self.ctnode
            return SQLResult(self.dbmode, self.cursor)

        elif self.dbmode == "cx_Oracle":
            import cx_Oracle
            if self.debug & 1:
                print queryString
            try:
                res = self.cursor.execute(queryString) # [ attribs, text, sub elements ]
            except cx_Oracle.Error, msg:
                raise DBConnectSQLResultException, msg
            return SQLResult(self.dbmode, res)
        elif self.dbmode == "db_bypass":
            res = self.dbcnx.query(queryString) # [ attribs, text, sub elements ]
            return SQLResult(self.dbmode, res)

        else:
            raise DBConnectException("Unrecognised database connection mode" + self.dbmode )


    def finish(self):
        if self.dbmode == "libpq":
            if self.dbcnx:
              self.dbcnx.finish()
        elif self.dbmode == "cx_Oracle" or self.dbmode == "psycopg2":
            self.cursor = None
            if self.dbcnx:
              self.dbcnx.close()
        elif self.dbmode == "db_bypass":
            if self.dbcnx:
              self.dbcnx.close()
        else:
            raise DBConnectException("Unrecognised database connection mode" + self.dbmode )
        if self.debug & 1:
          print self.dbname , " disconnected!\n"
        self.dbcnx = None




class SQLResult:
    " we need to transparently wrap libpq/cx_oracle sql result lists somehow. "
    def __init__(self, mode, result ):
        
        self.result = result
        self.dbmode = mode
        self.rcnt = -1
        if self.dbmode == "libpq":
            self.resultStatus = result.resultStatus
            self.nfields = result.nfields     # table columns
            self.ntuples = result.ntuples     # table rows
#            self.getvalue = result.getvalue   # func call(i,j)  - hopefully a reference??

        elif self.dbmode == "cx_Oracle" or self.dbmode == "psycopg2":
            """
You can fetch all the rows into a list, but this can have some bad side effects if the result set is large.
You have to wait for the entire result set to be returned to your client process.
You may eat up a lot of memory in your client to hold the built-up list.
It may take a while for Python to construct and deconstruct the list which you are going to immediately discard anyways.
            """
            self.ntuples = 0
            self.nfields = 0
            self.cachedata = []
            if result==None: return
            data =  result.fetchall() # potentially very expensive!!
            self.cachedata = data
            if data == None: pass 
            elif type(data) == type([]):
                self.ntuples = len(data)
                if self.ntuples <=0 or data[0] == None:
                   self.nfields = 0
                elif type(data[0]) == type([]) or type(data[0]) == type(()):
                   self.nfields = len(data[0])
                else:
                   self.nfields = 1
            else:
                self.ntuples = 1
                self.nfields = 1
            #print  self.ntuples, self.nfields
                #self.resultStatus = result.resultStatus
        elif self.dbmode == "db_bypass":
            self.cachedata = result
            #print "\nresult " , result
            self.ntuples = 0
            self.nfields = 0
            if result == None: pass 
            elif type(result) == type([]):
                self.ntuples = len(result)
                if self.ntuples <=0 or result[0] == None:
                   self.nfields = 0
                elif type(result[0]) == type(()):
                   self.nfields = len(result[0])
                else:
                   self.nfields = 1
            else:
                self.ntuples = 1
                self.nfields = 1
                self.cachedata = [[result]]
            #print  self.ntuples, self.nfields
            
        else:
            raise DBConnectSQLResultException("unknown database mode for result")
             
    def __iter__(self):
        return self                 # simplest iterator creation

    def next(self):
        if self.dbmode == "libpq":
            return self.result.next()
        elif self.dbmode == "cx_Oracle" or self.dbmode == "psycopg2":
            self.rcnt += 1  # increment current row count
            if self.rcnt >= len(self.cachedata):
               raise StopIteration
            return self.cachedata[self.rcnt]
        elif self.dbmode == "db_bypass":
            self.rcnt += 1  # increment current row count
            #print self.rcnt
            #if self.rcnt >= len(self.cachedata):
            if self.rcnt >= len(self.cachedata):
               raise StopIteration
            return self.cachedata[self.rcnt]
        raise StopIteration

    def getvalue(self,i,j):
        if self.dbmode == "libpq":
            return self.result.getvalue(i,j)
        elif self.dbmode == "cx_Oracle" or self.dbmode == "psycopg2":
            return self.cachedata[i][j]
        elif self.dbmode == "db_bypass":
            return self.cachedata[i][j]
        return None # or throw an exception ???

    def clear(self):
        
        if self.dbmode == "libpq":
            return self.result.clear()
        elif self.dbmode == "cx_Oracle" or self.dbmode == "psycopg2":
            self.cachedata = None
            result = self.result
            self.result = None
            #return result.close()
            return None
        elif self.dbmode == "db_bypass":
            self.cachedata = None
            self.result = None
        

class DummyDB:
    """ This class tries to emulate a database connection for offline testing. 
    - as per Carlos' request.
    Its inevitable that something will break when we return an inconsistent
    result, given the infinite possible complexities of SQL queries and results.
    """
    def __init__(self, mode):
	self.simMode = mode  # we are trying to simulate this one
	self.mode = "db_bypass" # but htis is who we really are
        import re
        self.split= re.compile("select\s(?P<fields>.+?)\sfrom\s(?P<tables>.*)", re.IGNORECASE)
    def close(self):
         pass

    def query(self, sqlString):
        """ Emulate an SQL query, but just return rubbish """
        print "DummyDB ",sqlString
        m = self.split.match(sqlString)
        fieldst = m.group('fields')
        tables = m.group('tables') 
        #print "tables",tables
        
        fields = fieldst.split(',') 
        fields = self.fixFields(fields)
        #print fields
        if len(fields) == 1:
            if fields[0].endswith("_id") or fields[0].endswith("_ID"):
                return 55 
            else:
                return "wibble"
        else:
            res = ()
	    for f in fields:
		if f.find("TO_DATE")!=-1:
                    import datetime
                    #import date
                    res += (datetime.date.today(), )
                elif f.endswith("_id") or f.endswith("_ID"):
                    res += (77,)	
                else:
                    res += ("wibble",)	
            if tables.find("BOOKINGS")!=-1:
		res = [res]   # dummy list
	    return res
        return  None

    def fixFields(self, list):
        """ function calls with multiple comma delimited fields screw up
        the initial splitting. Now we gotta fix it. """
        out = []
        save = ""
        for elem in list:
            save = save + elem 
            q = save.count('\'') 
            dq = save.count('"') 
            op = save.count('(')  
            cp = save.count(')')  
            if  q % 2 == 0 and  dq %2 == 0 and op - cp == 0:
		out.append(save)
		save = ""
        if save != "":
            print list
            raise DBConnectException("DummyDB cant parse the SQL query"  )
        return out

            
            
         
